﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CreateSubscriptionService]
	@ServiceName sysname
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @QueueName sysname
	DECLARE @SubscriptionActivationSP sysname
	
	SET @QueueName = 'SQ_' + @ServiceName
	SET @SubscriptionActivationSP = 'SubscriptionAct_' + @ServiceName
	
	DECLARE @sql nvarchar(1000)
	
	SET @sql =
		N'CREATE PROCEDURE ' + @SubscriptionActivationSP + N'
		  AS
		  BEGIN
			  DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
			  DECLARE @RecvReqMsg NVARCHAR(100);
			  DECLARE @RecvReqMsgName sysname;

			  WHILE (1=1)
			  BEGIN

				BEGIN TRANSACTION;

				WAITFOR
				( RECEIVE TOP(1)
					@RecvReqDlgHandle = conversation_handle,
					@RecvReqMsg = message_body,
					@RecvReqMsgName = message_type_name
				  FROM ' + @QueueName + N'
				), TIMEOUT 5000;

				IF (@@ROWCOUNT = 0)
				BEGIN
				  ROLLBACK TRANSACTION;
				  BREAK;
				END

				
				IF @RecvReqMsgName =
					N''http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog''
				BEGIN
				   END CONVERSATION @RecvReqDlgHandle;
				END
				ELSE IF @RecvReqMsgName =
					N''http://schemas.microsoft.com/SQL/ServiceBroker/Error''
				BEGIN
				   END CONVERSATION @RecvReqDlgHandle;
				END
			    
			     
				COMMIT TRANSACTION;

			  END

		  END'
	
	EXEC sp_executesql @sql
	
	SET @sql = N'CREATE QUEUE '+ @QueueName + N' WITH STATUS = ON, RETENTION = OFF ,
						   ACTIVATION (
								STATUS = OFF,
								PROCEDURE_NAME = ' + @SubscriptionActivationSP + N',
								MAX_QUEUE_READERS = 1, 
								EXECUTE AS SELF)'
   
   EXEC sp_executesql @sql
   
   SET @sql = N'CREATE SERVICE ' + @ServiceName + N' ON QUEUE ' + @QueueName + ' ([DEFAULT])'

   EXEC sp_executesql @sql	
   
END

